﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IOT4Week1Day1.Domain.Models;

using Microsoft.Data.SqlClient; //SqlServer的客户端程序
using EFCore.Extensions;
using IOT4Week1Day1.Domain.IRepositories;
using Microsoft.EntityFrameworkCore;
using Dapper;

namespace IOT4Week1Day1.EFCore.Repositories
{
    /// <summary>
    /// 用户的仓储接口
    /// </summary>
    public class UserRepository: EfRepository<User>, IUserRepository
    {
        private readonly MyDbContext _db;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db"></param>
        public UserRepository(MyDbContext db):base(db)
        {
            _db = db;
        }

        /// <summary>
        /// 写一个方法去调用分页存储过程
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="name">用户名模糊查询条件</param>
        /// <returns></returns>
        public (int total, List<User> list) GetPage(int pageIndex, int pageSize, string name, bool? status)
        {
            string strWhere = " and IsDeleted=0 "; //把逻辑删除的排除掉

            if (!string.IsNullOrEmpty(name))
            {
                strWhere += $" and Username like '%{name}%'";
            }
            if(status != null)
            {
                int s = status == true ? 1 : 0;
                strWhere += $" and IsActive={s}";
            }
            //1. 参数赋值
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@tableName", "[Users]"),
                new SqlParameter("@where", strWhere),
                new SqlParameter("@orderby", "UId asc"),
                new SqlParameter("@pageIndex", pageIndex),
                new SqlParameter("@pageSize", pageSize),
                new SqlParameter("@totalCount", System.Data.SqlDbType.Int),
            };
            paras[5].Direction = System.Data.ParameterDirection.Output;  //指明最后一个参数是 输出参数

            //2. efcore调用存储过程
            var list = _db.Query<User>("p_pageList", System.Data.CommandType.StoredProcedure, paras);

            //3. 获取到两个输出
            int total = Convert.ToInt32(paras[5].Value);

            return (total, list);
        }


        /// <summary>
        /// 演示如何用Dapper来调用存储过程
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public (int total, List<User> list) GetPage2(int pageIndex, int pageSize, string name)
        {
            string strWhere = string.Empty;

            if (!string.IsNullOrEmpty(name))
            {
                strWhere += $" and Username like '%{name}%'";
            }
            //1. Dapper动态参数赋值
            DynamicParameters paras = new DynamicParameters();
            paras.Add("@tableName", "[Users]");
            paras.Add("@where", strWhere);
            paras.Add("@orderby", "UId asc");
            paras.Add("@pageIndex", pageIndex);
            paras.Add("@pageSize", pageSize);
            paras.Add("@totalCount", dbType: System.Data.DbType.Int32,
                                     direction: System.Data.ParameterDirection.Output);

            //2. 获取上下文的数据库连接对象
            using var dbConnection = _db.Database.GetDbConnection();
            //3. 用Dapper扩展方法来执行存储过程
            var list = dbConnection.Query<User>("p_pageList",
                                        commandType: System.Data.CommandType.StoredProcedure,
                                        param: paras
                                      ).ToList();
            //3. 获取到两个输出
            int total = paras.Get<int>("@totalCount");

            return (total, list);
        }
    }
}
